package com.erpoa.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;

/**
 * @author chenjimin
 * @Title: CarProgressMapper
 * @ProjectName oaerp
 * @Description: TODO
 * @date 2020/11/215:03
 */
@Mapper
public interface CarProgressMapper {
    @Select(" select distinct cust_name CUSTNAME,(select count(*) from wms_review_detail h where h.tray_number=j.tray_number ) COUNT ," +
            " (select distinct to_char( def_out_date,'yyyy-MM-dd') from wms_review_detail h where h.tray_number=j.tray_number and rownum=1 ) defoutdate from wms_review_detail j " +
            " where TRAY_NUMBER =#{traynumber} ")
    HashMap selectByTrayNo(@Param("traynumber") String traynumber);

    @Select(" select count(*) from wms_review_entruck where TRAYNUMBER =  #{traynumber} ")
    int countByTrayNo(@Param("traynumber") String traynumber);

    @Insert("  insert into  wms_review_entruck  (traynumber,hrno,counttrno,CUSTNAME,DEFOUTDATE,CARCODE) values(#{traynumber},#{usercode},#{count},#{sname}," +
            " to_date(#{defoutdate},'yyyy-MM-dd'),#{carno}) ")
    int inserttrayNumber(@Param("traynumber") String traynumber, @Param("count") String count, @Param("sname") String sname,
                         @Param("usercode") String usercode, @Param("defoutdate") String defoutdate, @Param("carno") String carno);

    @Select(" with o_list as " +
            "         ( " +
            "         select lt.org_no,lt.list_no,ct.cust_no,ct.sname ,lt.dft_out_date " +
            "         from wms_out_list lt " +
            "         join  LMS_ORG_CUST ct on lt.org_no=ct.org_no and lt.cust_no=ct.cust_no and ct.sname=#{sname} " +
            "         where" +
            "          lt.dft_out_date =  to_date(#{defOutDate},'yyyy-MM-dd') " +
            "         ),o_line as ( " +
            "         select le.org_no,le.list_no,le.prod_no,le.prod_name,le.prod_spec,nvl(sum(le.qty),0) as sumqty, " +
            "         nvl((select max(pck_qty) from lms_prod_pack_modle lm where  le.prod_no=lm.prod_no),0) pck_qty " +
            "         from wms_out_line le " +
            "         where   le.create_date>add_months(sysdate,-3) " +
            "         group by  le.org_no,le.list_no,le.prod_no,le.prod_name,le.prod_spec " +
            "         ) " +
            //"         select    ole.prod_name PRODNAME,olt.list_no ||'('|| olt.org_no ||')' AREANO,round(ole.sumqty/ole.pck_qty,2)||'件' ORGNO " +
            "         select    nvl(sum(round(ole.sumqty/ole.pck_qty,2)),0) DESCALL " +
            "         from o_list olt " +
            "         join o_line ole on olt.org_no=ole.org_no and olt.list_no=ole.list_no  order by olt.list_no")
    String countdescAllbydefoutdate(@Param("sname") String sname, @Param("defOutDate") String defOutDate);

    @Select("  select nvl(sum(counttrno),0)  from wms_review_entruck where custname =#{sname} " +
            "and trunc(defoutdate) =trunc(to_date(#{defOutDate},'yyyy-MM-dd'))    ")
    String counthasscanBysnamedate(@Param("sname") String sname, @Param("defOutDate") String defOutDate);

    @Select(" select distinct carcode  CARCODE from wms_review_entruck  where  trunc(ENTRUCKDATE) between trunc(sysdate-1) and    trunc(sysdate) ")
    List<HashMap> getCarNo();

    @Select("   select distinct TRAY_NUMBER FBATCH from wms_review_detail  " +
            " where TRAY_NUMBER not in ( select TRAYNUMBER  from wms_review_entruck) " +
            " and cust_name =#{sName} and trunc(init_date) between trunc(sysdate-1) and trunc(sysdate) ")
    List<HashMap> selectuntrayNum(@Param("sName") String sName);
}
